﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.BOS.Core.Bill.PlugIn.Args;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ltd
{
    [HotUpdate]
    [Description("更新物料【最新实际体积到仓价】")]
    public class UpdateNewPrice : AbstractOperationServicePlugIn
    {
        public override void OnPreparePropertys(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.PreparePropertysEventArgs e)
        {
            e.FieldKeys.Add("FNumber");
            base.OnPreparePropertys(e);
        }

        public override void AfterExecuteOperationTransaction(AfterExecuteOperationTransaction e)
        {
            base.AfterExecuteOperationTransaction(e);
            foreach (DynamicObject item in e.DataEntitys)
            {
                string SQL = "/*dialect*/" + string.Format(@"
select t.FMATERIALID,t.F_TRAV_AMOUNT_QTR2
into #A
from (
SELECT t1.FMATERIALID, t1.F_TRAV_AMOUNT_QTR2, t.fdate
FROM T_PUR_Receive t
LEFT JOIN T_PUR_ReceiveEntry t1 ON t.FID = t1.FID
LEFT JOIN T_BD_MATERIAL t2 ON t1.FMATERIALID = t2.FMATERIALID
WHERE t.FDOCUMENTSTATUS = 'C'
AND t1.F_TRAV_AMOUNT_QTR2 <> 0
)t
inner join 
(
SELECT t1.FMATERIALID, max(t.fdate)fdate
FROM T_PUR_Receive t
LEFT JOIN T_PUR_ReceiveEntry t1 ON t.FID = t1.FID
LEFT JOIN T_BD_MATERIAL t2 ON t1.FMATERIALID = t2.FMATERIALID
WHERE t.FDOCUMENTSTATUS = 'C'
AND t1.F_TRAV_AMOUNT_QTR2 <> 0
group by t1.FMATERIALID
)t1 on t.FMATERIALID=t1.FMATERIALID and t.FDATE=t1.fdate
                UPDATE T SET T.FNEWPRICE=T1.F_TRAV_AMOUNT_QTR2   FROM T_BD_MATERIAL T
                LEFT JOIN #A T1 ON T.FMATERIALID=T1.FMATERIALID
                WHERE T1.F_TRAV_AMOUNT_QTR2 IS NOT NULL");
                DBUtils.ExecuteDynamicObject(this.Context, SQL);

                 SQL = "/*dialect*/" + string.Format(@"
    DELETE FROM TRAV_t_Cust_Entry100014
 INSERT INTO TRAV_t_Cust_Entry100014 (FENTRYID,FMATERIALID,FAMOUNT1,FMOUNT)
 SELECT ROW_NUMBER() OVER (ORDER BY FMATERIALID)FXH,FMATERIALID,F_TRAV_AMOUNT_QTR2,FDATE 
--INTO #B
FROM (
    SELECT t1.FMATERIALID, t2.FNUMBER, t1.F_TRAV_AMOUNT_QTR2, t.fdate,
           ROW_NUMBER() OVER (PARTITION BY YEAR(t.fdate), MONTH(t.fdate),t1.FMATERIALID ORDER BY t.fdate DESC) AS RowNumber
    FROM T_PUR_Receive t
    LEFT JOIN T_PUR_ReceiveEntry t1 ON t.FID = t1.FID
    LEFT JOIN T_BD_MATERIAL t2 ON t1.FMATERIALID = t2.FMATERIALID
    WHERE t.FDOCUMENTSTATUS = 'C'
    AND t1.F_TRAV_AMOUNT_QTR2 <> 0
) AS SubQuery
WHERE RowNumber = 1 ");
                DBUtils.ExecuteDynamicObject(this.Context, SQL);
            }
        }
    }
}
